///Author: Erzen Oncel
///Project: Introducing GLP Paper Table 3
///Date:14 09 2018 
///Stata14 

********TABLE 3*************Languages 10 

***Row 1****
clear all 
use "/Users/erzeno/Dropbox/GLP/JG_EO_Papers/Introducing GLP Paper/Publication/PoP/Submission 2 (Bernhard)/Round 3/Replicationdata/GLP_person_18 07 29.dta"
rename person_lang_all_count langcount
drop if langcount==0
sort country_nid
preserve
collapse (mean) langcount=langcount, by (country country_nid)
sum langcount
restore
**apex**
preserve
keep if power==1
collapse (mean) langcount=langcount, by (country country_nid)
sum langcount
restore
**next 10**
preserve
keep if power==3
collapse (mean) langcount=langcount, by (country country_nid)
sum langcount
restore
**cabinet**
preserve
keep if office1==2|office2==2|office3==2
collapse (mean) langcount=langcount, by (country country_nid)
sum langcount
restore
**Supreme court**
preserve
keep if office1==7|office2==7|office3==7
collapse (mean) langcount=langcount, by (country country_nid)
sum langcount
restore
**MPs**
preserve
keep if office1==6|office2==6|office3==6
collapse (mean) langcount=langcount, by (country country_nid)
sum langcount
restore
**Wealth**
*oecd*
preserve
keep if wealth==1
collapse (mean) langcount=langcount, by (country country_nid)
sum langcount
restore
*nonoecd*
preserve
keep if wealth==0
collapse (mean) langcount=langcount, by (country country_nid)
sum langcount
restore
**Regime**
*Democracy*
preserve
keep if regime==0
collapse (mean) langcount=langcount, by (country country_nid)
sum langcount
count
restore
*Nondemocracy*
preserve
keep if regime==1
collapse (mean) langcount=langcount, by (country country_nid)
sum langcount
restore
*regions
preserve
keep if wealth==0
collapse (mean) langcount=langcount, by (region country_nid)
by region, sort: sum langcount
restore


***Rows on Languages***
**English**
clear all
use "/Users/erzeno/Dropbox/GLP/JG_EO_Papers/Introducing GLP Paper/Publication/PoP/Submission 2 (Bernhard)/Round 3/Replicationdata/GLP_person_18 07 29.dta"
drop if missing(person_lang_all)
gen eng=0
replace eng=1 if english==1
gen noeng=0
replace noeng=1 if english==0
//collapse (sum) eng=noeng, by(country_nid)
preserve
collapse (sum) eng=eng noeng=noeng, by( country country_nid)
gen total=eng+noeng
gen prceng=eng/total*100
sum prceng
gen avgeng=r(mean)
restore
*Apex*
preserve
keep if power==1
collapse (sum) eng=eng noeng=noeng, by( country country_nid)
gen total=eng+noeng
gen prceng=eng/total*100
sum prceng
restore
*Top Ten*
preserve
keep if power==3
collapse (sum) eng=eng noeng=noeng, by( country country_nid)
gen total=eng+noeng
gen prceng=eng/total*100
sum prceng
restore
*Cabinet*
preserve
keep if office1==2|office2==2|office3==2
collapse (sum) eng=eng noeng=noeng, by( country country_nid)
gen total=eng+noeng
gen prceng=eng/total*100
sum prceng
restore
*Supreme Court*
preserve
keep if office1==7|office2==7|office3==7
collapse (sum) eng=eng noeng=noeng, by( country country_nid)
gen total=eng+noeng
gen prceng=eng/total*100
sum prceng
restore
*MPs*
preserve
keep if office1==6|office2==6|office3==6
collapse (sum) eng=eng noeng=noeng, by( country country_nid)
gen total=eng+noeng
gen prceng=eng/total*100
sum prceng
restore
*Wealth/Region/Regime*
preserve
collapse (sum) eng=eng noeng=noeng, by( country country_nid regime wealth region)
gen total=eng+noeng
gen prceng=eng/total*100
sum prceng
gen avgeng=r(mean)
sum prceng if wealth==0
sum prceng if wealth==1
sum prceng if regime==0
sum prceng if regime==1
by region, sort: sum prceng 

**French**
clear all
use "/Users/erzeno/Dropbox/GLP/JG_EO_Papers/Introducing GLP Paper/Publication/PoP/Submission 2 (Bernhard)/Round 3/Replicationdata/GLP_person_18 07 29.dta"
drop if missing(person_lang_all)
gen fren=0
replace fren=1 if french==1
gen nofren=0
replace nofren=1 if french==0
preserve
collapse (sum) fren=fren nofren=nofren, by( country country_nid)
gen total=fren+nofren
gen prcfrench=fren/total*100
sort fren
sum prcfrench
gen avgfrench=r(mean)
sort prcfrench
restore
*Apex*
preserve
keep if power==1
collapse (sum) fren=fren nofren=nofren, by( country country_nid)
gen total=fren+nofren
gen prcfrench=fren/total*100
sum prcfrench
restore
*Top Ten*
preserve
keep if power==3
collapse (sum) fren=fren nofren=nofren, by( country country_nid)
gen total=fren+nofren
gen prcfrench=fren/total*100
sum prcfrench
restore
*Cabinet*
preserve
keep if office1==2|office2==2|office3==2
collapse (sum) fren=fren nofren=nofren, by( country country_nid)
gen total=fren+nofren
gen prcfrench=fren/total*100
sum prcfrench
restore
*Supreme Court*
preserve
keep if office1==7|office2==7|office3==7
collapse (sum) fren=fren nofren=nofren, by( country country_nid)
gen total=fren+nofren
gen prcfrench=fren/total*100
sum prcfrench
restore
*MPs*
preserve
keep if office1==6|office2==6|office3==6
collapse (sum) fren=fren nofren=nofren, by( country country_nid)
gen total=fren+nofren
gen prcfrench=fren/total*100
sum prcfrench
restore
**Wealth/Region/Regime**
preserve
collapse (sum) fren=fren nofren=nofren, by( country country_nid wealth regime region)
gen total=fren+nofren
gen prcfrench=fren/total*100
sort fren
sum prcfrench
sum prcfrench if wealth==0
sum prcfrench if wealth==1
sum prcfrench if regime==0
sum prcfrench if regime==1
by region, sort: sum prcfrench
restore

**Spanish**
clear all
use "/Users/erzeno/Dropbox/GLP/JG_EO_Papers/Introducing GLP Paper/Publication/PoP/Submission 2 (Bernhard)/Round 3/Replicationdata/GLP_person_18 07 29.dta"
drop if missing(person_lang_all)
gen span=0
replace span=1 if spanish==1
gen nospan=0
replace nospan=1 if spanish==0
preserve
collapse (sum) span=span nospan=nospan, by (country country_nid)
gen total=span+nospan
gen prcspan=span/total*100
sum prcspan
gen avgspan=r(mean)
restore
*Apex*
preserve
keep if power==1
collapse (sum) span=span nospan=nospan, by( country country_nid)
gen total=span+nospan
gen prcspan=span/total*100
sum prcspan
restore
*Next 10*
preserve
keep if power==3
collapse (sum) span=span nospan=nospan, by( country country_nid)
gen total=span+nospan
gen prcspan=span/total*100
sum prcspan
restore
*Cabinet*
preserve
keep if office1==2|office2==2|office3==2
collapse (sum) span=span nospan=nospan, by( country country_nid)
sort span
gen total=span+nospan
gen prcspan=span/total*100
sum prcspan
restore
*Supreme Court*
preserve
keep if office1==7|office2==7|office3==7
collapse (sum) span=span nospan=nospan, by( country country_nid)
gen total=span+nospan
gen prcspan=span/total*100
sum prcspan
restore
*MPs* 
preserve
keep if office1==6|office2==6|office3==6
collapse (sum) span=span nospan=nospan, by( country country_nid)
gen total=span+nospan
gen prcspan=span/total*100
sum prcspan
restore
**Wealth/Region/Regime**
preserve
collapse (sum) span=span nospan=nospan, by (country country_nid wealth regime region)
gen total=span+nospan
gen prcspan=span/total*100
sum prcspan
sum prcspan if wealth==0
sum prcspan if wealth==1
sum prcspan if regime==0
sum prcspan if regime==1
by region, sort: sum prcspan
restore

**Arabic**
clear all
use "/Users/erzeno/Dropbox/GLP/JG_EO_Papers/Introducing GLP Paper/Publication/PoP/Submission 2 (Bernhard)/Round 3/Replicationdata/GLP_person_18 07 29.dta"
drop if missing(person_lang_all)
gen arab=0
replace arab=1 if arabic==1
gen noarab=0
replace noarab=1 if arabic==0
preserve
collapse (sum) arab=arab noarab=noarab, by (country country_nid)
gen total=arab+noarab
gen prcarab=arab/total*100
sum prcarab
gen avgarab=r(mean)
sort prcarab
restore
*Apex*
preserve
keep if power==1
collapse (sum) arab=arab noarab=noarab, by( country country_nid)
gen total=arab+noarab
gen prcarab=arab/total*100
sum prcarab
restore
*Top Ten*
preserve
keep if power==3
collapse (sum) arab=arab noarab=noarab, by( country country_nid)
gen total=arab+noarab
gen prcarab=arab/total*100
sum prcarab
restore
*Cabinet*
preserve
keep if office1==2|office2==2|office3==2
collapse (sum) arab=arab noarab=noarab, by( country country_nid)
gen total=arab+noarab
gen prcarab=arab/total*100
sum prcarab
restore
*Supreme Court* 
preserve
keep if office1==7|office2==7|office3==7
collapse (sum) arab=arab noarab=noarab, by( country country_nid)
gen total=arab+noarab
gen prcarab=arab/total*100
sum prcarab
restore
*MPs*
preserve
keep if office1==6|office2==6|office3==6
collapse (sum) arab=arab noarab=noarab, by( country country_nid)
gen total=arab+noarab
gen prcarab=arab/total*100
sum prcarab
restore
**Wealth/Region/Regime**
preserve
collapse (sum) arab=arab noarab=noarab, by (country country_nid wealth regime region)
gen total=arab+noarab
gen prcarab=arab/total*100
sum prcarab
sum prcarab if wealth==0
sum prcarab if wealth==1
sum prcarab if regime==0
sum prcarab if regime==1
by region, sort: sum prcarab
restore

**Russian**
clear all
use "/Users/erzeno/Dropbox/GLP/JG_EO_Papers/Introducing GLP Paper/Publication/PoP/Submission 2 (Bernhard)/Round 3/Replicationdata/GLP_person_18 07 29.dta"
drop if missing(person_lang_all)
gen rus=0
replace rus =1 if russian==1
gen norus=0
replace norus=1 if russian==0
preserve
collapse (sum) rus=rus norus=norus, by (country country_nid)
gen total= rus+norus
gen prcrus=rus/total*100
sum prcrus
gen avgrus=r(mean)
sort prcrus
restore
*Apex*
preserve
keep if power==1
collapse (sum) rus=rus norus=norus, by (country country_nid)
gen total= rus+norus
gen prcrus=rus/total*100
sum prcrus
restore
**Top Ten**
preserve
keep if power==3
collapse (sum) rus=rus norus=norus, by (country country_nid)
gen total= rus+norus
gen prcrus=rus/total*100
sum prcrus
restore
*Cabinet*
preserve
keep if office1==2|office2==2|office3==2
collapse (sum) rus=rus norus=norus, by (country country_nid)
gen total= rus+norus
gen prcrus=rus/total*100
sum prcrus
restore
*Supreme Court*
preserve
keep if office1==7|office2==7|office3==7
collapse (sum) rus=rus norus=norus, by (country country_nid)
gen total= rus+norus
gen prcrus=rus/total*100
sum prcrus
restore
*MPs* 
preserve
keep if office1==6|office2==6|office3==6
collapse (sum) rus=rus norus=norus, by (country country_nid)
gen total= rus+norus
gen prcrus=rus/total*100
sum prcrus
restore
*Wealth/Region/Regime*
preserve
collapse (sum) rus=rus norus=norus, by (country country_nid wealth regime region)
gen total= rus+norus
gen prcrus=rus/total*100
sum prcrus
sum prcrus if wealth==0
sum prcrus if wealth==1
sum prcrus if regime==0
sum prcrus if regime==1
by region, sort: sum  prcrus
restore

**German**
clear all
use "/Users/erzeno/Dropbox/GLP/JG_EO_Papers/Introducing GLP Paper/Publication/PoP/Submission 2 (Bernhard)/Round 3/Replicationdata/GLP_person_18 07 29.dta"
drop if missing(person_lang_all)
gen ger=0
replace ger =1 if german==1
gen noger=0
replace noger=1 if german==0
preserve
collapse (sum) ger=ger noger=noger, by (country country_nid)
gen total=ger+noger
gen prcger=ger/total*100
replace prcger =round(prcger)
sum prcger
gen avgger=r(mean)
sort prcger
restore
*Apex*
preserve
keep if power==1
collapse (sum) ger=ger noger=noger, by (country country_nid)
gen total=ger+noger
gen prcger=ger/total*100
sum prcger
restore
*Top Ten*
preserve
keep if power==3
collapse (sum) ger=ger noger=noger, by (country country_nid)
gen total=ger+noger
gen prcger=ger/total*100
sum prcger
restore
*Cabinet* 
preserve
keep if office1==2|office2==2|office3==2
collapse (sum) ger=ger noger=noger, by (country country_nid)
gen total=ger+noger
gen prcger=ger/total*100
sum prcger
restore
*Supreme Court*
preserve
keep if office1==7|office2==7|office3==7
collapse (sum) ger=ger noger=noger, by (country country_nid)
gen total=ger+noger
gen prcger=ger/total*100
sum prcger
restore
*MPs*
preserve
keep if office1==6|office2==6|office3==6
collapse (sum) ger=ger noger=noger, by (country country_nid)
gen total=ger+noger
gen prcger=ger/total*100
sum prcger
restore
**Wealth/Region/Regime**
preserve
collapse (sum) ger=ger noger=noger, by (country country_nid wealth region regime)
gen total=ger+noger
gen prcger=ger/total*100
replace prcger =round(prcger)
sum prcger
sum prcger if wealth==0
sum prcger if wealth==1
sum prcger if regime==0
sum prcger if regime==1
by region, sort: sum  prcger
restore

**Portuguese**
clear all
use "/Users/erzeno/Dropbox/GLP/JG_EO_Papers/Introducing GLP Paper/Publication/PoP/Submission 2 (Bernhard)/Round 3/Replicationdata/GLP_person_18 07 29.dta"
drop if missing(person_lang_all)
gen por=0
replace por =1 if portuguese==1
gen nopor=0
replace nopor=1 if portuguese==0
preserve
collapse (sum) por=por nopor=nopor, by (country country_nid)
gen total=por+nopor
gen prcpor=por/total*100
sum prcpor
gen avgpor=r(mean)
sort prcpor
restore
*Apex*
preserve
keep if power==1
collapse (sum) por=por nopor=nopor, by (country country_nid)
gen total=por+nopor
gen prcpor=por/total*100
sum prcpor
restore
*Top Ten*
preserve
keep if power==3
collapse (sum) por=por nopor=nopor, by (country country_nid)
gen total=por+nopor
gen prcpor=por/total*100
sum prcpor
restore
*Cabinet*
preserve
keep if office1==2|office2==2|office3==2
collapse (sum) por=por nopor=nopor, by (country country_nid)
gen total=por+nopor
gen prcpor=por/total*100
sum prcpor
restore
*Supreme Court*
preserve
keep if office1==7|office2==7|office3==7
collapse (sum) por=por nopor=nopor, by (country country_nid)
gen total=por+nopor
gen prcpor=por/total*100
sum prcpor
restore
*MPs*
preserve
keep if office1==6|office2==6|office3==6
collapse (sum) por=por nopor=nopor, by (country country_nid)
gen total=por+nopor
gen prcpor=por/total*100
sum prcpor
restore
**Wealth/Region/Regime**
preserve
collapse (sum) por=por nopor=nopor, by (country country_nid wealth regime region)
gen total=por+nopor
gen prcpor=por/total*100
sum prcpor
sum prcpor if wealth==0
sum prcpor if wealth==1
sum prcpor if regime==0
sum prcpor if regime==1
by region, sort: sum  prcpor
restore

**Chinese**
clear all
use "/Users/erzeno/Dropbox/GLP/JG_EO_Papers/Introducing GLP Paper/Publication/PoP/Submission 2 (Bernhard)/Round 3/Replicationdata/GLP_person_18 07 29.dta"
drop if missing(person_lang_all)
gen chin=0
replace chin=1 if chinese==1
gen nochin=0
replace nochin=1 if chinese==0
preserve
collapse (sum) chin=chin nochin=nochin, by (country country_nid)
gen total= chin+nochin
gen prcchin= chin/total*100
sum prcchin
gen avgchin=r(mean)
sort prcchin
restore
*Apex*
preserve
keep if power==1
collapse (sum) chin=chin nochin=nochin, by (country country_nid)
gen total= chin+nochin
gen prcchin= chin/total*100
sum prcchin
restore
*Top Ten*
preserve
keep if power==3
collapse (sum) chin=chin nochin=nochin, by (country country_nid)
gen total= chin+nochin
gen prcchin= chin/total*100
sum prcchin
restore
*Cabinet*
preserve
keep if office1==2|office2==2|office3==2
collapse (sum) chin=chin nochin=nochin, by (country country_nid)
gen total= chin+nochin
gen prcchin= chin/total*100
sum prcchin
restore
*Supreme Court*
preserve
keep if office1==7|office2==7|office3==7
collapse (sum) chin=chin nochin=nochin, by (country country_nid)
gen total= chin+nochin
gen prcchin= chin/total*100
sum prcchin
restore
*MPs*
preserve
keep if office1==6|office2==6|office3==6
collapse (sum) chin=chin nochin=nochin, by (country country_nid)
gen total= chin+nochin
gen prcchin= chin/total*100
sum prcchin
restore
**Wealth/Region/Regime**
preserve
collapse (sum) chin=chin nochin=nochin, by (country country_nid wealth regime region)
gen total= chin+nochin
gen prcchin= chin/total*100
sum prcchin
sum prcchin if wealth==0
sum prcchin if wealth==1
sum prcchin if regime==0
sum prcchin if regime==1
by region, sort: sum  prcchin
restore

**Other**
clear all
use "/Users/erzeno/Dropbox/GLP/JG_EO_Papers/Introducing GLP Paper/Publication/PoP/Submission 2 (Bernhard)/Round 3/Replicationdata/GLP_person_18 07 29.dta"
drop if missing(person_lang_all)
gen oth=0
replace oth =1 if otherlang==1
gen nooth=0
replace nooth=1 if otherlang==0
preserve
collapse (sum) oth=oth nooth=nooth, by (country country_nid)
gen total=oth+nooth
gen prcother=oth/total*100
sum prcother
gen avgoth=r(mean)
sort prcother
restore
*Apex*
preserve
keep if power==1
collapse (sum) oth=oth nooth=nooth, by (country country_nid)
gen total=oth+nooth
gen prcother=oth/total*100
sum prcother
restore
*Top Ten*
preserve
keep if power==3
collapse (sum) oth=oth nooth=nooth, by (country country_nid)
gen total=oth+nooth
gen prcother=oth/total*100
sum prcother
restore
*Cabinet*
preserve
keep if office1==2|office2==2|office3==2
collapse (sum) oth=oth nooth=nooth, by (country country_nid)
gen total=oth+nooth
gen prcother=oth/total*100
sum prcother
restore
*Supreme Court*
preserve
keep if office1==7|office2==7|office3==7
collapse (sum) oth=oth nooth=nooth, by (country country_nid)
gen total=oth+nooth
gen prcother=oth/total*100
sum prcother
restore
*MPs*
preserve
keep if office1==6|office2==6|office3==6
collapse (sum) oth=oth nooth=nooth, by (country country_nid)
gen total=oth+nooth
gen prcother=oth/total*100
sum prcother
restore
**Wealth/Region/Regime**
preserve
collapse (sum) oth=oth nooth=nooth, by (country country_nid wealth regime region)
gen total=oth+nooth
gen prcother=oth/total*100
sum prcother
sum prcother if wealth==0
sum prcother if wealth==1
sum  prcother if regime==0
sum  prcother if regime==1
by region, sort: sum  prcother
restore
